﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Fitment.App_Code
{
    public class ProDateMng
    {
        private int houseId;         //房子ID
        private string workType;     //工种类型
        private DateTime startDate;  //该工种项目的开始日期
        private DS data;

        public ProDateMng()
        {
            houseId = 0;
            workType = "";
            startDate = DateTime.Now;
            data = new DS();
        }
        public ProDateMng(int houseId, string workType, DateTime startDate)
        {
            this.houseId = houseId;
            this.workType = workType;
            this.startDate = startDate;
            data = new DS();
        }
        public bool ArrangeDate()
        {
            string sql = "SELECT MATERIAL.PRONAME, SUM(MATERIAL.NEEDTIME) AS PRODAY "
                + "FROM HOUSEINFO, ROOMINFO, SELECTITEM, MATERIAL "
                + "WHERE HOUSEINFO.HOUSE_ID = ROOMINFO.HOUSE_ID AND ROOMINFO.ROOM_ID = SELECTITEM.ROOM_ID "
                + "AND SELECTITEM.MAT_ID = MATERIAL.MAT_ID AND HOUSEINFO.HOUSE_ID = '" + this.houseId + "' "
                + "AND MATERIAL.WORKTYPE = '" + this.workType + "' "
                + "GROUP BY MATERIAL.PRONAME ORDER BY MATERIAL.PRONAME ASC";
            DataTable table = data.GetDataSource(sql, "info");
            int count = table.Rows.Count;
            string proName = "";
            for (int i = 0; i < (count - 1); i++)
            {
                proName += "PROJECTNAME = '" + table.Rows[i][0].ToString() + "' OR ";
            }
            proName += "PROJECTNAME = '" + table.Rows[count - 1][0].ToString() + "' ";

            sql = "SELECT PRO_ID, PROJECTNAME, STARTDATE, FINISHDATE, [STATE] FROM PROJECTINFO "
                + "WHERE (" + proName + ") AND HOUSE_ID = '" + this.houseId + "' ORDER BY PROJECTNAME ASC";
            string strConnection = ConfigurationManager.ConnectionStrings["connection"].ToString();
            SqlConnection conn = new SqlConnection(strConnection);   ///创建数据库连接
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            SqlCommandBuilder bd = new SqlCommandBuilder(da);
            try
            {
                da.Fill(ds, "Table");
                DataTable tb = ds.Tables["table"];
                DateTime start = this.startDate;
                for (int i = 0; i < count; i++)
                {
                    tb.Rows[i]["STARTDATE"] = start;    //项目起始日期
                    start = start.AddDays(Convert.ToInt32(table.Rows[i]["PRODAY"].ToString()));
                    tb.Rows[i]["FINISHDATE"] = start.AddDays(-1);
                    tb.Rows[i]["STATE"] = 'n';
                }
                da.Update(tb);
                tb.AcceptChanges();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                conn.Close();
                da.Dispose();
            }
            return true;
        }

        public int HouseId
        {
            get { return houseId; }
            set { houseId = value; }
        }
        public string WorkType
        {
            get { return workType; }
            set { workType = value; }
        }
        public DateTime StartDate
        {
            get { return startDate; }
            set { startDate = value; }
        }
    }
}
